
package com.zhonghong.excel;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class ExcelUtil {
    /** Excel对象 */
    private Workbook mWorkBook;

    public ExcelUtil(File excelFile) {
        init(excelFile);
    }
    
    // 该构造函数只是为了调用 createAndWriteExcel
    public ExcelUtil() {
    }
    
    private void init(File excelFile) {
        try {
            WorkbookSettings setting = new WorkbookSettings();
            setting.setEncoding("utf-8");;
            mWorkBook = Workbook.getWorkbook(excelFile, setting);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 读取某一张Sheet,默认读取第一张表(索引值为0)
     * 默认读取三列(支持三种语言)
     */
    public HashMap<Integer, ArrayList<String>> readSheet() {
        return readSheet(0);
    }

    /**
     * 读取某一张Sheet(默认读取三列)
     * 
     * @param sheetIndex ： sheet索引值,默认为0
     */
    private HashMap<Integer, ArrayList<String>> readSheet(int sheetIndex) {
        HashMap<Integer, ArrayList<String>> map = new HashMap<Integer, ArrayList<String>>();
        // 获取excel对象的第一张表 sheet1
        Sheet sheet = mWorkBook.getSheet(sheetIndex);
        try {
            for (int i = 0; i < 3; i++) {
                ArrayList<String> tempList = new ArrayList<String>();
                Cell[] cells = sheet.getColumn(i);
                for (Cell cell : cells) {
                    String content = cell.getContents();
                    tempList.add(content);
                }
                map.put(i, tempList);
            }
        } catch (ArrayIndexOutOfBoundsException e) {
            System.out.println("数组越界检查");
            e.printStackTrace();
            return map;
        }
        return map;
    }
    
    /**
     * 根据strings.xml文件的键值创建并写入Excel表格中的第一列
     * @param outPath 生成的Excel文件的路径,包含文件名后缀
     * @param keys strings.xml文件的键值集合
     * @return
     */
    public boolean createAndWriteExcel(String outPath, List<String> keys)   {
        // 创建Excel工作簿
        WritableWorkbook workBook;
        try {
            OutputStream outStream = new FileOutputStream(outPath);
            workBook = Workbook.createWorkbook(outStream);
            WritableSheet sheet = workBook.createSheet("strings.xml的键值", 0);
            for (int i = 0; i < keys.size(); i++) {
                sheet.addCell(new Label(0, i , keys.get(i)));
            }
            workBook.write();
            workBook.close();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }
    
    /**
     * 将键值对集合写入到Excel文件
     * @param outPath
     * @param keyValues
     * @return
     */
    public boolean writeKeyValue2Excel(String outPath, List<String>[] keyValues) {
        // 创建Excel工作簿
        WritableWorkbook workBook;
        try {
            OutputStream outStream = new FileOutputStream(outPath);
            workBook = Workbook.createWorkbook(outStream);
            WritableSheet sheet = workBook.createSheet("键值对", 0);
            for (int i = 0; i < keyValues[0].size(); i++) {
                sheet.addCell(new Label(0, i , keyValues[0].get(i)));
                sheet.addCell(new Label(1, i , keyValues[1].get(i)));
            }
            workBook.write();
            workBook.close();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }
    
//    public static void main(String[] args) {
//        ExcelUtil excelUtil = new ExcelUtil();
//        XmlUtil xmlUtil = new XmlUtil();
//        ArrayList<String> lists = xmlUtil.readSrcXml(new File("strings.xml"));
//        excelUtil.createAndWriteExcel("D://test.xls", lists);
//    }
    
    /**
     * 测试方法
     * @param args
     */
//    public static void main(String[] args) {
//        ExcelUtil util = new ExcelUtil(new File("appAudio.xls"));
//        util.readSheet();
//    }

    /**
     * 关闭打开的WorkBook
     */
    public void closeWorkBook() {
        this.mWorkBook.close();
    }

}
